const db = require('../config/database');
const moment = require('moment');
const { decrypt } = require('../utils/encryption');

// 解码函数
function decodeUserData(encodedData) {
  if (!encodedData) return null;
  try {
    return decrypt(encodedData);
  } catch (error) {
    console.error('解密用户数据失败:', error);
    return encodedData; // 如果解密失败，返回原数据
  }
}

class Clue {
  constructor(data = {}) {
    this.id = data.id;
    this.clue_no = data.clue_no;
    this.user_id = data.user_id;
    this.category_id = data.category_id;
    this.category = data.category;
    this.title = data.title;
    this.content = data.content;
    this.suspect_info = data.suspect_info;
    this.incident_time = data.incident_time;
    this.incident_location = data.incident_location;
    this.status = data.status || 'pending';
    this.handler_id = data.handler_id;
    this.handle_note = data.handle_note;
    this.priority = data.priority || 1;
    this.is_public = data.is_public || 0;
    this.view_count = data.view_count || 0;
    this.created_at = data.created_at;
    this.updated_at = data.updated_at;
  }

  // 创建线索
  async create() {
    try {
      // 生成案件编号
      if (!this.clue_no) {
        this.clue_no = await this.generateClueNo();
      }

      const clueData = {
        clue_no: this.clue_no,
        user_id: this.user_id,
        category_id: this.category_id,
        category: this.category,
        title: this.title,
        content: this.content,
        suspect_info: this.suspect_info,
        incident_time: this.incident_time,
        incident_location: this.incident_location,
        status: this.status,
        priority: this.priority
      };

      const result = await db.insert('clues', clueData);
      this.id = result.insertId;
      return this;
    } catch (error) {
      throw new Error(`创建线索失败: ${error.message}`);
    }
  }

  // 生成案件编号
  async generateClueNo() {
    try {
      const now = new Date();
      const datePrefix = moment().format('YYYYMMDD');
      const timePrefix = moment().format('HHmmss');
      const prefix = `CL${datePrefix}`;
      
      // 添加重试机制，最多重试5次
      for (let attempt = 1; attempt <= 5; attempt++) {
        try {
          // 查询当天最大序号
          const sql = `
            SELECT clue_no FROM clues 
            WHERE clue_no LIKE ? 
            ORDER BY clue_no DESC 
            LIMIT 1
          `;
          const rows = await db.query(sql, [`${prefix}%`]);
          
          let sequence = 1;
          if (rows.length > 0) {
            const lastNo = rows[0].clue_no;
            const lastSequence = parseInt(lastNo.substr(-4));
            sequence = lastSequence + 1;
          }
          
          // 添加毫秒时间戳确保唯一性
          const milliseconds = now.getMilliseconds().toString().padStart(3, '0');
          const clueNo = `${prefix}${sequence.toString().padStart(4, '0')}`;
          
          // 检查是否已存在
          const existingClue = await db.findOne('clues', { clue_no: clueNo });
          if (!existingClue) {
            return clueNo;
          }
          
          // 如果存在，等待一小段时间后重试
          await new Promise(resolve => setTimeout(resolve, 10 * attempt));
          
        } catch (error) {
          if (attempt === 5) {
            throw error;
          }
          // 等待后重试
          await new Promise(resolve => setTimeout(resolve, 50 * attempt));
        }
      }
      
      // 如果所有重试都失败，使用时间戳作为后缀
      const timestamp = Date.now().toString().slice(-4);
      return `${prefix}${timestamp}`;
      
    } catch (error) {
      throw new Error(`生成案件编号失败: ${error.message}`);
    }
  }

  // 根据ID查找线索
  static async findById(id) {
    try {
      const clueData = await db.findOne('clues', { id });
      if (!clueData) return null;

      return new Clue(clueData);
    } catch (error) {
      throw new Error(`查找线索失败: ${error.message}`);
    }
  }

  // 根据案件编号查找线索
  static async findByClueNo(clueNo) {
    try {
      const clueData = await db.findOne('clues', { clue_no: clueNo });
      if (!clueData) return null;

      return new Clue(clueData);
    } catch (error) {
      throw new Error(`查找线索失败: ${error.message}`);
    }
  }

  // 获取线索列表
  static async getList(options = {}) {
    try {
      const { 
        page = 1, 
        pageSize = 20, 
        category, 
        status, 
        startDate, 
        endDate, 
        keyword,
        handlerId,
        priority,
        clueIds
      } = options;
      const offset = (page - 1) * pageSize;

      let conditions = {};
      let whereClause = 'WHERE 1=1';
      let params = [];

      // 如果提供了线索ID列表，优先按ID查询
      if (clueIds && Array.isArray(clueIds) && clueIds.length > 0) {
        const placeholders = clueIds.map(() => '?').join(',');
        whereClause += ` AND c.id IN (${placeholders})`;
        params.push(...clueIds);
      } else {
        // 否则使用其他筛选条件
        if (category) {
          whereClause += ' AND category = ?';
          params.push(category);
        }

        if (status) {
          whereClause += ' AND status = ?';
          params.push(status);
        }

        if (handlerId) {
          whereClause += ' AND handler_id = ?';
          params.push(handlerId);
        }

        if (priority) {
          whereClause += ' AND priority = ?';
          params.push(priority);
        }

        if (startDate) {
          whereClause += ' AND c.created_at >= ?';
          params.push(startDate);
        }

        if (endDate) {
          whereClause += ' AND c.created_at <= ?';
          params.push(endDate);
        }

        if (keyword) {
          whereClause += ' AND (title LIKE ? OR content LIKE ? OR clue_no LIKE ?)';
          const keywordPattern = `%${keyword}%`;
          params.push(keywordPattern, keywordPattern, keywordPattern);
        }
      }

      // 查询线索列表
      const sql = `
        SELECT c.*, u.name as reporter_name, u.phone as reporter_phone,
               u.is_anonymous,
               a.real_name as handler_name,
               (SELECT COUNT(*) FROM attachments att WHERE att.clue_id = c.id AND att.is_deleted = 0) as attachment_count
        FROM clues c
        LEFT JOIN users u ON c.user_id = u.id
        LEFT JOIN admins a ON c.handler_id = a.id
        ${whereClause}
        ORDER BY c.created_at DESC
        LIMIT ? OFFSET ?
      `;
      params.push(pageSize, offset);

      const clues = await db.query(sql, params);

      // 解码用户信息
      const decodedClues = clues.map(clue => ({
        ...clue,
        reporter_name: decodeUserData(clue.reporter_name),
        reporter_phone: decodeUserData(clue.reporter_phone)
      }));

      // 查询总数
      const countSql = `SELECT COUNT(*) as total FROM clues c ${whereClause}`;
      const countParams = params.slice(0, -2); // 移除LIMIT和OFFSET参数
      const countResult = await db.query(countSql, countParams);
      const total = countResult[0].total;

      return {
        list: decodedClues,
        pagination: {
          page: parseInt(page),
          pageSize: parseInt(pageSize),
          total,
          totalPages: Math.ceil(total / pageSize)
        }
      };
    } catch (error) {
      throw new Error(`获取线索列表失败: ${error.message}`);
    }
  }

  // 获取线索详情（包含用户信息和附件）
  static async getDetail(id) {
    try {
      const sql = `
        SELECT c.*, 
               u.name as reporter_name, u.phone as reporter_phone, 
               u.email as reporter_email, u.province, u.city, u.district, u.address,
               u.is_anonymous,
               a.real_name as handler_name,
               cc.name as category_name
        FROM clues c
        LEFT JOIN users u ON c.user_id = u.id
        LEFT JOIN admins a ON c.handler_id = a.id
        LEFT JOIN clue_categories cc ON c.category_id = cc.id
        WHERE c.id = ?
      `;
      
      const rows = await db.query(sql, [id]);
      if (rows.length === 0) return null;

      const clueData = rows[0];

      // 解码用户信息
      if (clueData.reporter_name) {
        clueData.reporter_name = decodeUserData(clueData.reporter_name);
      }
      if (clueData.reporter_phone) {
        clueData.reporter_phone = decodeUserData(clueData.reporter_phone);
      }
      if (clueData.reporter_email) {
        clueData.reporter_email = decodeUserData(clueData.reporter_email);
      }

      // 获取附件列表
      const attachments = await db.findMany('attachments', 
        { clue_id: id, is_deleted: 0 },
        { orderBy: 'upload_time DESC' }
      );

      return {
        ...clueData,
        attachments
      };
    } catch (error) {
      throw new Error(`获取线索详情失败: ${error.message}`);
    }
  }

  // 更新线索状态
  async updateStatus(status, handlerId = null, handleNote = '') {
    try {
      const updateData = {
        status,
        handle_note: handleNote,
        updated_at: new Date()
      };

      if (handlerId) {
        updateData.handler_id = handlerId;
      }

      const affectedRows = await db.update('clues', updateData, { id: this.id });
      
      if (affectedRows > 0) {
        this.status = status;
        this.handler_id = handlerId;
        this.handle_note = handleNote;
        return true;
      }
      return false;
    } catch (error) {
      throw new Error(`更新线索状态失败: ${error.message}`);
    }
  }

  // 增加查看次数
  async incrementViewCount() {
    try {
      const sql = 'UPDATE clues SET view_count = view_count + 1 WHERE id = ?';
      await db.query(sql, [this.id]);
      this.view_count += 1;
    } catch (error) {
      console.error('增加查看次数失败:', error);
    }
  }

  // 批量更新线索状态
  static async batchUpdateStatus(clueIds, status, handlerId = null) {
    try {
      let sql = 'UPDATE clues SET status = ?, updated_at = NOW()';
      let params = [status];

      if (handlerId) {
        sql += ', handler_id = ?';
        params.push(handlerId);
      }

      sql += ' WHERE id IN (' + clueIds.map(() => '?').join(',') + ')';
      params.push(...clueIds);

      const result = await db.query(sql, params);
      return result.affectedRows;
    } catch (error) {
      throw new Error(`批量更新线索状态失败: ${error.message}`);
    }
  }

  // 获取统计数据
  static async getStatistics(options = {}) {
    try {
      const { startDate, endDate, groupBy = 'status' } = options;
      
      let whereClause = 'WHERE 1=1';
      let params = [];

      if (startDate) {
        whereClause += ' AND c.created_at >= ?';
        params.push(startDate);
      }

      if (endDate) {
        whereClause += ' AND c.created_at <= ?';
        params.push(endDate);
      }

      let sql;
      if (groupBy === 'status') {
        sql = `
          SELECT status, COUNT(*) as count
          FROM clues
          ${whereClause}
          GROUP BY status
        `;
      } else if (groupBy === 'category') {
        sql = `
          SELECT category, COUNT(*) as count
          FROM clues
          ${whereClause}
          GROUP BY category
        `;
      } else if (groupBy === 'date') {
        sql = `
          SELECT DATE(created_at) as date, COUNT(*) as count
          FROM clues
          ${whereClause}
          GROUP BY DATE(created_at)
          ORDER BY date DESC
          LIMIT 30
        `;
      }

      const result = await db.query(sql, params);
      return result;
    } catch (error) {
      throw new Error(`获取统计数据失败: ${error.message}`);
    }
  }

  // 获取概览统计
  static async getOverview(period = 'all') {
    try {
      let dateCondition = '';
      if (period === 'today') {
        dateCondition = "AND DATE(created_at) = CURDATE()";
      } else if (period === 'week') {
        dateCondition = "AND created_at >= DATE_SUB(NOW(), INTERVAL 7 DAY)";
      } else if (period === 'month') {
        dateCondition = "AND created_at >= DATE_SUB(NOW(), INTERVAL 30 DAY)";
      }

      const sql = `
        SELECT 
          COUNT(*) as total_clues,
          COUNT(CASE WHEN status = 'pending' THEN 1 END) as pending_clues,
          COUNT(CASE WHEN status = 'processing' THEN 1 END) as processing_clues,
          COUNT(CASE WHEN status = 'completed' THEN 1 END) as completed_clues,
          COUNT(CASE WHEN status = 'closed' THEN 1 END) as closed_clues,
          COUNT(CASE WHEN DATE(created_at) = CURDATE() THEN 1 END) as today_clues
        FROM clues 
        WHERE 1=1 ${dateCondition}
      `;

      const result = await db.query(sql);
      return result[0];
    } catch (error) {
      throw new Error(`获取概览统计失败: ${error.message}`);
    }
  }

  // 更新线索信息
  async update(updateData) {
    try {
      console.log('Clue.update 开始:', {
        clueId: this.id,
        updateData,
        currentPriority: this.priority
      });

      // 允许更新的字段
      const allowedFields = [
        'title', 'content', 'category', 'category_id', 'priority', 
        'suspect_info', 'incident_time', 'incident_location', 'handle_note'
      ];
      
      const filteredData = {};
      Object.keys(updateData).forEach(key => {
        if (allowedFields.includes(key) && updateData[key] !== undefined) {
          filteredData[key] = updateData[key];
        }
      });
      
      console.log('过滤后的数据:', filteredData);
      
      if (Object.keys(filteredData).length === 0) {
        console.log('没有有效的更新字段');
        return false;
      }
      
      filteredData.updated_at = new Date();
      
      console.log('准备执行数据库更新:', {
        table: 'clues',
        data: filteredData,
        where: { id: this.id }
      });
      
      const affectedRows = await db.update('clues', filteredData, { id: this.id });
      
      console.log('数据库更新结果:', { affectedRows });
      
      if (affectedRows > 0) {
        // 更新当前对象的属性
        Object.keys(filteredData).forEach(key => {
          this[key] = filteredData[key];
        });
        console.log('更新成功，新的优先级:', this.priority);
        return true;
      }
      console.log('更新失败: affectedRows = 0');
      return false;
    } catch (error) {
      console.error('Clue.update 错误:', error);
      throw new Error(`更新线索信息失败: ${error.message}`);
    }
  }

  // 转换为JSON格式
  toJSON() {
    return {
      id: this.id,
      clue_no: this.clue_no,
      user_id: this.user_id,
      category_id: this.category_id,
      category: this.category,
      title: this.title,
      content: this.content,
      suspect_info: this.suspect_info,
      incident_time: this.incident_time,
      incident_location: this.incident_location,
      status: this.status,
      handler_id: this.handler_id,
      handle_note: this.handle_note,
      priority: this.priority,
      is_public: this.is_public,
      view_count: this.view_count,
      created_at: this.created_at,
      updated_at: this.updated_at
    };
  }
}

module.exports = Clue; 